﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using GenericTools;
using System.Data.Common;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using DataModels.Common;
using DataModels.Enumerations;
using GenericTools.Models;

namespace ReportingManagement
{
    public partial class ImportReportData : Form
    {
        #region Class Constants
        const int MAX_ROW_INDEX = 10;
        const int INITIAL_ROW_INDEX = 1;
        const string FACILITY_ROW_START_TEXT = "PMTCT Antenatal Clinic (ANC)";

        const string ANC_TITLE = @"PMTCT Antenatal Clinic (ANC) Monthly Summary Form";
        const string PCR_TITLE = @"PMTCT Care (PCR) Monthly Summary Form";
        const string MAT_TITLE = @"PMTCT Labor & Delivery (L&D) Monthly Summary Form";
        const string MC_TITLE = @"PMTCT Mother Child Follw up (MC) Monthly Summary Form";

        const int ANC_INDICATORS_OFFSET = 14;
        const int PCR_INDICATORS_OFFSET = 13;
        const int MAT_INDICATORS_OFFSET = 19;  // one row is just blank, don't know why, yet...
        const int MC_INDICATORS_OFFSET = 20;

        const int ANC_INDICATORS = 14;
        const int PCR_INDICATORS = 13;
        const int MAT_INDICATORS = 18; 
        const int MC_INDICATORS = 20;

        const string ANC = "ANC";
        const string PCR = "PCR";
        const string MAT = "MAT";
        const string MC = "MC";
        #endregion

        #region Class Structures
        struct ImportProgress
        {
            public ReportingMessage reportingMessage;
            public string log;
            bool forSubTitle;
        }
        #endregion

        #region Class Variables
        private UserInformation user = null;
        private bool loaded = false;
        private bool verified = false;
        private bool canBeClosed = true;

        private DataImportLog importedReportLog = null;
        private DataImportLog existingReportLog = null;
        private DataImportLog failedReportLog = null;

        private DateTime importFileLastDateModified = DateTime.Now;
        private int totalReportsInFile = 0;
        private int totalReportSubmitted = 0;
        private int totalReportAccepted = 0;
        private int totalReportRejected = 0;
        private int totalRejectAlreadyExisted = 0;
        private Dictionary<string, Dictionary<string, Dictionary<string, List<int>>>> facilitiesMonthData = null;

        private DateTime startTime;
        private DataTable facilities = null;
        #endregion

        #region Constructors
        public ImportReportData(UserInformation user)
        {
            InitializeComponent();
            this.user = user;
            FillUserInformation();
            
            btnVerifyFile.Enabled = false;
            btnImportData.Enabled = false;
            proBar.Visible = false;

            facilitiesMonthData = new Dictionary<string, Dictionary<string, Dictionary<string, List<int>>>>();            
            importedReportLog = new DataImportLog(DataImportLog.ContainerType.LIST_BOX);
            importedReportLog.Text = "Imported Reports Log";

            existingReportLog = new DataImportLog(DataImportLog.ContainerType.LIST_BOX);
            existingReportLog.Text = "Already Existing Reports Log";

            failedReportLog = new DataImportLog(DataImportLog.ContainerType.LIST_BOX);
            failedReportLog.Text = "Failed Reports Log";
        }
        #endregion

        #region Event Handlers
        private void ImportReportData_Load(object sender, EventArgs e)
        {
            this.loaded = true;
            rdoNation.Enabled = false;
            rdoRegion.Enabled = false;
            rdoDistrict.Checked = true;
            rdoFacility.Enabled = false;
        }        

        private void cboRegions_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                int regionID = int.Parse(cboRegions.SelectedValue.ToString());
                Cursor.Current = Cursors.WaitCursor;
                DataTable dataTable = Utilities.GetDistrictsByRegion(regionID);
                Cursor.Current = Cursors.Default;
                cboDistricts.ValueMember = "DistrictNo";
                cboDistricts.DisplayMember = "District";
                cboDistricts.DataSource = dataTable;
                verified = false;
            }
            catch (Exception ex)
            {
                if (loaded) DisplayErrorMessage(ex);
            }
        }

        private void cboDistricts_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                int districtID = Int32.Parse(cboDistricts.SelectedValue.ToString());
                Cursor.Current = Cursors.WaitCursor;
                DataTable dataTable = Utilities.GetFacilitiesDetailsByDistrict(districtID);
                Cursor.Current = Cursors.Default;
                cboFacilities.ValueMember = "FacCode";
                cboFacilities.DisplayMember = "FacName";
                cboFacilities.DataSource = dataTable;
                this.facilities = dataTable;
                verified = false;
            }
            catch (Exception ex)
            {
                if (loaded) DisplayErrorMessage(ex);
            }
        }

        private void rdoNation_CheckedChanged(object sender, EventArgs e)
        {
            if (rdoNation.Checked)
            {
                lblRegion.Enabled = false;
                lblDistrict.Enabled = false;
                lblFacility.Enabled = false;

                cboRegions.Enabled = false;
                cboDistricts.Enabled = false;
                cboFacilities.Enabled = false;
                verified = false;
            }
        }

        private void rdoRegion_CheckedChanged(object sender, EventArgs e)
        {
            if (rdoRegion.Checked)
            {
                lblRegion.Enabled = true;
                lblDistrict.Enabled = false;
                lblFacility.Enabled = false;

                cboRegions.Enabled = true;
                cboDistricts.Enabled = false;
                cboFacilities.Enabled = false;

                verified = false;
            }
        }

        private void rdoDistrict_CheckedChanged(object sender, EventArgs e)
        {
            if (rdoDistrict.Checked)
            {
                lblRegion.Enabled = true;
                lblDistrict.Enabled = true;
                lblFacility.Enabled = false;

                cboRegions.Enabled = true;
                cboDistricts.Enabled = true;
                cboFacilities.Enabled = false;

                verified = false;
            }
        }

        private void rdoFacility_CheckedChanged(object sender, EventArgs e)
        {
            if (rdoFacility.Checked)
            {
                lblRegion.Enabled = true;
                lblDistrict.Enabled = true;
                lblFacility.Enabled = true;

                cboRegions.Enabled = true;
                cboDistricts.Enabled = true;
                cboFacilities.Enabled = true;

                verified = false;
            }
        }  

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            DialogResult response = openFileDialog.ShowDialog(this);
            if (response == System.Windows.Forms.DialogResult.OK)
            {
                string filename = openFileDialog.FileName;
                txtImportFile.Text = filename;
                btnImportData.Enabled = true;
                btnVerifyFile.Enabled = true;
                verified = false;
            }
        }

        private void btnVerifyFile_Click(object sender, EventArgs e)
        {
            if (importedReportLog.Visible) importedReportLog.Hide();
            if (existingReportLog.Visible) existingReportLog.Hide();
            if (failedReportLog.Visible) failedReportLog.Hide();

            totalReportsInFile = 0;
            txtLog.Clear();
            gboAccessLevel.Enabled = false;
            canBeClosed = false;
            btnBrowse.Enabled = false;
            btnVerifyFile.Enabled = false;
            btnImportData.Enabled = false;
            lblLog.Text = "Verifying import file, Please wait...";
            lblLog.Visible = true;
            lblTimeElapsed.Visible = false;

            #region Verifying Workbook data
            Excel.Application excelApplication = null;
            Excel.Workbooks excelWorkbooks = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Sheets excelSheets = null;
            Excel.Worksheet excelWorksheet = null;
            Excel.Range cells = null;
            Excel.Range cell = null;

            try
            {
                excelApplication = new Excel.Application();
                excelWorkbooks = excelApplication.Workbooks;
                excelWorkbook = excelWorkbooks.Open(
                    txtImportFile.Text, 0, true, 5, "", "", true,
                    Excel.XlPlatform.xlWindows, "\t", false, false, 0, false, 1, 0);

                #region Going through every sheet
                excelSheets = excelWorkbook.Sheets;
                int count = excelSheets.Count;
                bool errorOccured = false;
                this.facilitiesMonthData.Clear();    // Clear everything
                for (int i = 1; i <= count; i++)
                {
                    if (errorOccured) break;
                    excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(i);
                    string sheetName = excelWorksheet.Name;
                    string log = String.Empty;
                    log = String.Format("[Reading sheet: {0}]{1}", sheetName, Environment.NewLine);
                    txtLog.AppendText(log);
                    Application.DoEvents();
                    log = "Validating sheet name";
                    txtLog.AppendText(log);
                    bool okToContinue = ValidSheetName(sheetName);
                    if (okToContinue)
                    {
                        txtLog.AppendText(" - Passed");
                        txtLog.AppendText(Environment.NewLine);
                        Application.DoEvents();
                        log = String.Format("[{0}]: Validating facility row", sheetName);
                        txtLog.AppendText(log);
                        int facilityRow = GetFacilityRow(excelWorksheet);
                        if (facilityRow != -1)
                        {
                            txtLog.AppendText(" - Passed");
                            txtLog.AppendText(Environment.NewLine);
                            Application.DoEvents();

                            List<Dictionary<string, string>> facilities = new List<Dictionary<string, string>>();
                            Dictionary<string, Dictionary<string, List<int>>> facilitiesData =
                                new Dictionary<string, Dictionary<string, List<int>>>();

                            #region Going through each facility in the sheet
                            int startingFacilityIndex = 2;
                            int facilityColumn = startingFacilityIndex;
                            while (true)
                            {
                                if (errorOccured) break;
                                cells = excelWorksheet.Cells;
                                cell = (Excel.Range)cells[facilityRow, facilityColumn];
                                string cellValue = (string)cell.Value;
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(cell); 
                                cell = null;
                                
                                if (String.IsNullOrEmpty(cellValue)) break;
                                else
                                {
                                    if (cellValue.ToLower() == "total") break;
                                    else
                                    {
                                        string facilityName = cellValue;
                                        string id = cboDistricts.SelectedValue.ToString();
                                        int districtID = Int32.Parse(id);
                                        log = String.Format("[{0}]: Validating facility: {1}", sheetName, facilityName);
                                        txtLog.AppendText(log);
                                        Application.DoEvents();

                                        string facilityCode = GetFacilityCode(facilityName, districtID);
                                        Dictionary<string, List<int>> facilityData = new Dictionary<string, List<int>>();
                                        if (!String.IsNullOrEmpty(facilityCode))
                                        {
                                            #region Determining Facility Data
                                            txtLog.AppendText(" - Passed");
                                            txtLog.AppendText(Environment.NewLine);
                                            Application.DoEvents();

                                            Dictionary<string, int> reportsTypeInfo = new Dictionary<string, int>();
                                            reportsTypeInfo[ANC] = ANC_INDICATORS_OFFSET;
                                            reportsTypeInfo[PCR] = PCR_INDICATORS_OFFSET;
                                            reportsTypeInfo[MAT] = MAT_INDICATORS_OFFSET;
                                            reportsTypeInfo[MC] = MC_INDICATORS_OFFSET;

                                            Dictionary<string, int> reportsIndicatorCount = new Dictionary<string, int>();
                                            reportsIndicatorCount[ANC] = ANC_INDICATORS;
                                            reportsIndicatorCount[PCR] = PCR_INDICATORS;
                                            reportsIndicatorCount[MAT] = MAT_INDICATORS;
                                            reportsIndicatorCount[MC] = MC_INDICATORS;

                                            Dictionary<string, string> reportsTypeTitle = new Dictionary<string, string>();
                                            reportsTypeTitle[ANC] = ANC_TITLE;
                                            reportsTypeTitle[PCR] = PCR_TITLE;
                                            reportsTypeTitle[MAT] = MAT_TITLE;
                                            reportsTypeTitle[MC] = MC_TITLE;

                                            int rowDataIndex = facilityRow + 1;
                                            foreach (string reportTypeInfo in reportsTypeInfo.Keys)
                                            {
                                                log = String.Format("[{0}]: Validating {1} {2} data", sheetName, facilityName, reportTypeInfo);
                                                txtLog.AppendText(log);
                                                Application.DoEvents();                                                
                                                int finalReportyTypeRowDataIndex = rowDataIndex + reportsTypeInfo[reportTypeInfo];
                                                List<int> facilityReportTypeData = new List<int>();
                                                #region Determining Report Data
                                                int foundIndicatorsCount = 0;
                                                for (; rowDataIndex < finalReportyTypeRowDataIndex; rowDataIndex++)
                                                {
                                                    if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                    cell = null;

                                                    cell = (Excel.Range)cells[rowDataIndex, facilityColumn];
                                                    string dataCellValue = null;
                                                    if (cell.Value == null)
                                                    {
                                                        // Now make sure this row does not have row header text
                                                        if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                        cell = null;

                                                        cell = (Excel.Range)cells[rowDataIndex, INITIAL_ROW_INDEX];
                                                        if (cell.Value == null)
                                                        {
                                                            if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                            cell = null;
                                                            continue;  // this is for empty row in the middle
                                                        }
                                                        else
                                                        {
                                                            errorOccured = true;
                                                            if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                            cell = null;

                                                            cell = (Excel.Range)cells[rowDataIndex, INITIAL_ROW_INDEX];
                                                            string rowHeaderText = (string)cell.Value;
                                                            MessageBox.Show(this,
                                                                String.Format("Sheet {0} is not valid for import. {3}{3}" +
                                                                    "At least one or more value of {1} of {2} is invalid. {3}{3}{3}" +
                                                                    "Please make sure the following value is valid integer:{3}{3}{4}",
                                                                    sheetName, reportsTypeTitle[reportTypeInfo],
                                                                    facilityName, Environment.NewLine, rowHeaderText),
                                                                AppConfiguration.ApplicationName,
                                                                MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                                            System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                            cell = null;
                                                            break;
                                                        }
                                                    }
                                                    else
                                                    {
                                                        dataCellValue = cell.Value.ToString();
                                                        if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                        cell = null;
                                                    }
                                                    if (!String.IsNullOrEmpty(dataCellValue))
                                                    {
                                                        try
                                                        {
                                                            int iData = Int32.Parse(dataCellValue);
                                                            facilityReportTypeData.Add(iData);
                                                            foundIndicatorsCount++;
                                                        }
                                                        catch
                                                        {
                                                            errorOccured = true;
                                                            if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                            cell = null;

                                                            cell = (Excel.Range)cells[rowDataIndex, INITIAL_ROW_INDEX];
                                                            string rowHeaderText = (string)cell.Value;
                                                            MessageBox.Show(this,
                                                                String.Format("Sheet {0} is not valid for import. {3}{3}" +
                                                                    "At least one or more value of {1} of {2} is invalid. {3}{3}{3}" +
                                                                    "Please make sure the following value is valid integer{3}{3}{4}",
                                                                    sheetName, reportsTypeTitle[reportTypeInfo], 
                                                                    facilityName, Environment.NewLine, rowHeaderText),
                                                                AppConfiguration.ApplicationName,
                                                                MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                                            System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                            cell = null;
                                                            break;
                                                        }
                                                    }
                                                    else
                                                    {
                                                        errorOccured = true;
                                                        if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                        cell = null;

                                                        cell = (Excel.Range)cells[rowDataIndex, INITIAL_ROW_INDEX];
                                                        string rowHeaderText = (string)cell.Value;
                                                        MessageBox.Show(this,
                                                            String.Format("Sheet {0} is not valid for import. {3}{3}" +
                                                                "At least one or more value of {1} of {2} is invalid. {3}{3}{3}" +
                                                                "Please make sure the following value is valid integer{3}{3}{4}",
                                                                sheetName, reportsTypeTitle[reportTypeInfo], 
                                                                facilityName, Environment.NewLine, rowHeaderText),
                                                            AppConfiguration.ApplicationName,
                                                            MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                                        System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                        cell = null;
                                                        break;
                                                    }
                                                    if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                    cell = null;
                                                }
                                                if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                                cell = null;
                                                #endregion

                                                if (errorOccured)
                                                {
                                                    txtLog.AppendText(" - Failed");
                                                    txtLog.AppendText(Environment.NewLine);
                                                    Application.DoEvents();
                                                    break;
                                                }
                                                else
                                                {
                                                    if (foundIndicatorsCount != reportsIndicatorCount[reportTypeInfo])
                                                    {
                                                        errorOccured = true;
                                                        txtLog.AppendText(" - Failed");
                                                        txtLog.AppendText(Environment.NewLine);
                                                        Application.DoEvents();
                                                        MessageBox.Show(this,
                                                            String.Format("Sheet {0} is not valid for import. {3}{3}" +
                                                                "At least one or more value of {1} of {2} is invalid. {3}{3}{3}" +
                                                                "Please make sure {4} has only {5} indicators. {6} indicators are present",
                                                                sheetName, reportsTypeTitle[reportTypeInfo],
                                                                facilityName, Environment.NewLine, 
                                                                reportsTypeTitle[reportTypeInfo], 
                                                                reportsIndicatorCount[reportTypeInfo], foundIndicatorsCount),
                                                            AppConfiguration.ApplicationName,
                                                            MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                                        break;
                                                    }
                                                    else
                                                    {
                                                        txtLog.AppendText(" - Passed");
                                                        txtLog.AppendText(Environment.NewLine);
                                                        Application.DoEvents();
                                                        totalReportsInFile++;
                                                    }
                                                }

                                                facilityData[reportTypeInfo] = facilityReportTypeData;
                                                rowDataIndex += 2;
                                            }
                                            if (errorOccured) break;

                                            string key = String.Format("{0}|{1}", facilityCode, facilityName);
                                            facilitiesData[key] = facilityData;
                                            facilityColumn++;
                                            txtLog.AppendText(Environment.NewLine);
                                            Application.DoEvents();
                                            #endregion
                                        }
                                        else
                                        {
                                            errorOccured = true;
                                            txtLog.AppendText(" - Failed");
                                            txtLog.AppendText(Environment.NewLine);
                                            Application.DoEvents();
                                            MessageBox.Show(this,
                                                String.Format("Sheet {0} is not valid for importing. " + Environment.NewLine +
                                                    Environment.NewLine + "Facility code of " + facilityName +
                                                    " could not be determined." + Environment.NewLine +
                                                    "Please make sure the name is the same as the one registered in the system.",
                                                sheetName),
                                                AppConfiguration.ApplicationName,
                                                MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                            if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                            cell = null;
                                            break;
                                        }
                                    }
                                }
                                if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                                if (cells != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cells);
                                cell = null;
                                cells = null;
                            }
                            if (cell != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                            if (cells != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(cells);
                            cell = null;
                            cells = null;
                            #endregion
                            this.facilitiesMonthData[sheetName] = facilitiesData;
                        }
                        else
                        {
                            errorOccured = true;
                            txtLog.AppendText(" - Failed");
                            txtLog.AppendText(Environment.NewLine);
                            Application.DoEvents();
                            MessageBox.Show(this,
                                String.Format("Sheet {0} is not valid for importing. " + Environment.NewLine +
                                    Environment.NewLine + "Make sure the title of the first report type is in the first column " +
                                    " and between row" + INITIAL_ROW_INDEX + " and " + MAX_ROW_INDEX + "." +
                                    Environment.NewLine + "The same row should also contain the facilities names.",
                                    sheetName),
                                AppConfiguration.ApplicationName,
                                MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            if (excelWorksheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                            excelWorksheet = null;
                            break;
                        }
                    }
                    else
                    {
                        errorOccured = true;
                        txtLog.AppendText(" - Failed");
                        txtLog.AppendText(Environment.NewLine);
                        Application.DoEvents();
                        MessageBox.Show(this,
                            String.Format("Sheet {0} is not valid for importing. " + Environment.NewLine +
                                Environment.NewLine + "Change its name to acceptable format: MMM YYYY. " +
                                Environment.NewLine + "For example Mar 1987 or Sep 2012.", sheetName),
                            AppConfiguration.ApplicationName,
                            MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        if (excelWorksheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                        excelWorksheet = null;
                        break;
                    }
                    if (excelWorksheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelSheets != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheets);
                excelSheets = null;
                #endregion
                if (errorOccured) verified = false;
                else
                {
                    FileInfo importFile = new FileInfo(txtImportFile.Text);
                    importFileLastDateModified = importFile.LastWriteTime;
                    verified = true;
                    string log = String.Format("Verification of data import files completed. Found {0} reports", totalReportsInFile);
                    txtLog.AppendText(log);
                    System.Media.SystemSounds.Asterisk.Play();
                }

                //excelWorkbook.Close();
                excelApplication.Quit();

                ReleaseObject(cell);
                ReleaseObject(cells);
                ReleaseObject(excelWorksheet);
                ReleaseObject(excelSheets);
                ReleaseObject(excelWorkbook);
                ReleaseObject(excelWorkbooks);
                ReleaseObject(excelApplication);
            }
            catch (Exception ex)
            {
                DisplayErrorMessage(ex);                
            }
            #endregion

            canBeClosed = true;
            gboAccessLevel.Enabled = true;
            btnBrowse.Enabled = true;
            btnVerifyFile.Enabled = true;
            btnImportData.Enabled = true;
            lblLog.Visible = false;
        }

        private void btnImportData_Click(object sender, EventArgs e)
        {
            if ((AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DATA_ACCESS) ||
                (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
            {
                MessageBox.Show(this,
                    String.Format("You can't import data to the server in offline mode.{0}{0}" +
                        "You can only validate the import file. If you want to import report data " +
                        "make sure you have internet connection and login using online mode" +
                        "(uncheck Use Offline data in login form)", Environment.NewLine),
                    AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }

            if (this.user.Level != AccessLevel.ADMINISTRATOR)
            {
                MessageBox.Show(this,
                    "You can't import data to the server if you're not system administrator." +
                    "Contact your system administrator for further assistance",
                    AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }

            try
            {
                btnBrowse.Enabled = false;
                gboAccessLevel.Enabled = false;
                btnVerifyFile.Enabled = false;
                btnImportData.Enabled = false;
                lblTimeElapsed.Visible = true;
                timeElapsedTimer.Start();
                startTime = DateTime.Now;
                Application.DoEvents();

                if (!verified) btnVerifyFile_Click(null, null);
                else
                {
                    // File may be modified without changing any component in the user interface
                    FileInfo importFile = new FileInfo(txtImportFile.Text);
                    if (importFileLastDateModified != importFile.LastWriteTime) btnVerifyFile_Click(null, null);
                }

                if (verified)
                {
                    proBar.Visible = true;
                    lblLog.Visible = true;
                    txtLog.Clear();

                    //Form mainForm = (Form)this.Parent.top;
                    //this.Top = this.Owner.Top;    // moving this form to coincide with parent form top
                    Point mainFormLocation = this.Owner.Bounds.Location;
                    Point parentLocation = new Point();
                    parentLocation.X = this.Location.X;
                    parentLocation.Y = this.Owner.Location.Y;
                    this.Location = parentLocation;
                    //Application.DoEvents();

                    parentLocation = this.Bounds.Location;
                    importedReportLog.ClearLog();
                    if (!importedReportLog.Visible)
                    {
                        Point location = new Point();
                        location.X = parentLocation.X - importedReportLog.Bounds.Width;
                        location.Y = parentLocation.Y;
                        importedReportLog.Height = this.Height;
                        importedReportLog.Location = location;
                        importedReportLog.Show(this);
                    }

                    existingReportLog.ClearLog();
                    if (!existingReportLog.Visible)
                    {
                        Point location = new Point();
                        location.X = parentLocation.X + this.Bounds.Width;
                        location.Y = parentLocation.Y;
                        existingReportLog.Height = this.Height;
                        existingReportLog.Location = location;
                        existingReportLog.Show(this);
                    }

                    failedReportLog.ClearLog();
                    if (!failedReportLog.Visible)
                    {
                        Screen display = Screen.PrimaryScreen;
                        Rectangle deskotp = display.WorkingArea;
                        int availableHeight = deskotp.Height - parentLocation.Y - this.Bounds.Height;

                        Point location = new Point();
                        location.X = parentLocation.X - importedReportLog.Bounds.Width;
                        location.Y = parentLocation.Y + this.Height;
                        failedReportLog.Width = this.Bounds.Width + importedReportLog.Bounds.Width + existingReportLog.Bounds.Width;
                        failedReportLog.Height = availableHeight;
                        failedReportLog.Location = location;
                        failedReportLog.Show(this);
                    }

                    proBar.Minimum = 0;
                    proBar.Maximum = totalReportsInFile;

                    totalReportSubmitted = 0;
                    totalReportAccepted = 0;
                    totalReportRejected = 0;
                    totalRejectAlreadyExisted = 0;

                    backgroundWorker.RunWorkerAsync();
                }
                else
                {
                    MessageBox.Show(this,
                        @"Import operation could not be completed. Verify data integrity in the source file",
                        AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    timeElapsedTimer.Stop();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this,
                    String.Format(@"The following error while processing your request{0}{0}{1}",
                        Environment.NewLine, ex.Message),
                    AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                timeElapsedTimer.Stop();
            }
        }        

        private void ImportReportData_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (canBeClosed)
            {
                if (importedReportLog.Visible)
                {
                    importedReportLog.Close();
                    importedReportLog.Dispose();
                }
                if (existingReportLog.Visible)
                {
                    existingReportLog.Close();
                    existingReportLog.Dispose();
                }
                if (failedReportLog.Visible)
                {
                    failedReportLog.Close();
                    failedReportLog.Dispose();
                }
                timeElapsedTimer.Stop();
            }
            else
            {
                if (backgroundWorker.IsBusy)
                {
                    btnCancel_Click(null, null);
                    if (!canBeClosed) e.Cancel = true;
                }
                else e.Cancel = true;
            }
        }

        #region Background Worker Event Handlers
        private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                int progress = 0;
                canBeClosed = false;
                string senderNum = String.Format("{0} [{1} Level]", this.user.FullName, this.user.AccessLevelText);
                foreach (string month in this.facilitiesMonthData.Keys)
                {
                    string period = month.Substring(0, 3);
                    string year = month.Substring(4, 4);
                    Dictionary<string, Dictionary<string, List<int>>> monthData = this.facilitiesMonthData[month];
                    foreach (string facility in monthData.Keys)
                    {
                        Dictionary<string, List<int>> facilityData = monthData[facility];
                        string[] parts = facility.Split('|');
                        string code = parts[0];
                        string name = parts[1];
                        foreach (string reportType in facilityData.Keys)
                        {
                            List<int> reportData = facilityData[reportType];
                            StringBuilder sBuilder = new StringBuilder();
                            sBuilder.Append(reportType);
                            sBuilder.Append(".");
                            sBuilder.Append(code);
                            sBuilder.Append(",");
                            sBuilder.Append(period);
                            sBuilder.Append(",");
                            sBuilder.Append(year);
                            foreach (int x in reportData)
                            {
                                sBuilder.Append(",");
                                sBuilder.Append(x);
                            }
                            string reportingText = sBuilder.ToString();
                            string log = String.Format("{0} {1} {2} {3} report", period, year, name, reportType);
                            backgroundWorker.ReportProgress(progress, log);

                            progress++;
                            backgroundWorker.ReportProgress(progress, null);
                            //if (reportType != "PCR") continue;
                            ReportingMessage reportingMessage = Utilities.SendReport(senderNum, reportingText/*, user.UserSignature*/, false);
                            totalReportSubmitted++;

                            switch (reportingMessage.Status)
                            {
                                case ReportingError.NO_ERROR_LABEL:
                                    log = String.Format("{0} {1} {2} {3} report", period, year, name, reportType);
                                    break;

                                case ReportingError.REPORT_ALREADY_SUBMITTED_LABEL:
                                    log = String.Format("{0} {1} {2} {3} report", period, year, name, reportType);
                                    break;

                                default:
                                    log = String.Format("{0} {1} {2} {3} report", period, year, name, reportType);
                                    break;
                            }
                            ImportProgress state = new ImportProgress();
                            state.log = log;
                            state.reportingMessage = reportingMessage;
                            backgroundWorker.ReportProgress(progress, state);

                            if (reportingMessage.Status == ReportingError.NO_ERROR_LABEL)
                            {
                                log = String.Format(" - Done{0}", Environment.NewLine);
                                backgroundWorker.ReportProgress(progress, log);
                            }
                            else
                            {
                                log = String.Format(" - Failed{0}", Environment.NewLine);
                                backgroundWorker.ReportProgress(progress, log);
                            }
                            if (backgroundWorker.CancellationPending)
                            {
                                log = String.Format("{0}Operation cancelled by the user{0}", Environment.NewLine);
                                backgroundWorker.ReportProgress(progress, log);
                                return;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                backgroundWorker.ReportProgress(-1, ex);
            }
        }

        private void backgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            if (e.UserState == null)
            {
                string log = String.Format("Submitting Report {0} of {1}", e.ProgressPercentage, totalReportsInFile);
                lblLog.Text = log;
                return;
            }

            try
            {
                ImportProgress importProgress = (ImportProgress)e.UserState;
                ReportingMessage reportingMessage = importProgress.reportingMessage;
                proBar.Value = e.ProgressPercentage;
                switch (reportingMessage.Status)
                {
                    case ReportingError.NO_ERROR_LABEL:
                        totalReportAccepted++;
                        importedReportLog.AddLogLine(importProgress.log);
                        break;

                    case ReportingError.REPORT_ALREADY_SUBMITTED_LABEL:
                        totalRejectAlreadyExisted++;
                        existingReportLog.AddLogLine(importProgress.log);
                        break;

                    default:
                        totalReportRejected++;
                        string log = String.Format("[{0}]: {1}", importProgress.log, reportingMessage.Message);
                        failedReportLog.AddLogLine(log);
                        break;
                }
            }
            catch
            {
                try
                {
                    string log = (string)e.UserState;
                    txtLog.AppendText(log);
                }
                catch
                {
                    Exception ex = (Exception)e.UserState;
                    MessageBox.Show(this,
                        String.Format(@"The following error while processing your request{0}{0}{1}",
                            Environment.NewLine, ex.Message),
                        AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            timeElapsedTimer.Stop();
            txtLog.AppendText(Environment.NewLine);
            txtLog.AppendText("Data importing completed successfully");
            MessageBox.Show(this,
                String.Format("The operation completed successfully. {0}{0}" +
                    "Reports submitted: {1} of {5}{0}" +
                    "Reports accepted: {2}{0}" +
                    "Reports already existed: {3}{0}" +
                    "Reports rejected: {4}{0}" +
                    "{0}For more details check the logs.",
                    Environment.NewLine, totalReportSubmitted, totalReportAccepted,
                    totalRejectAlreadyExisted, totalReportRejected, totalReportsInFile),
                AppConfiguration.ApplicationName,
                MessageBoxButtons.OK, MessageBoxIcon.Information);

            proBar.Visible = false;
            btnBrowse.Enabled = true;
            gboAccessLevel.Enabled = true;
            btnVerifyFile.Enabled = true;
            btnImportData.Enabled = true;
            canBeClosed = true;
            lblLog.Visible = false;
        }
        #endregion

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (backgroundWorker.IsBusy)
            {
                DialogResult response = MessageBox.Show(this,
                    "Are you sure you want to cancel the current operation?",
                    AppConfiguration.ApplicationName,
                    MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (response == System.Windows.Forms.DialogResult.Yes)
                {
                    btnCancel.Enabled = false;
                    backgroundWorker.CancelAsync();
                }
            }
        }
        #endregion

        #region Private Helper Methods
        private void DisplayErrorMessage(Exception ex)
        {
            Cursor.Current = Cursors.Default;
            string message = String.Format("{0}\n\nError Details:\n{1}",
                "Please make sure you have internet connection", ex.Message);
            MessageBox.Show(this, message, AppConfiguration.ApplicationName,
                MessageBoxButtons.OK, MessageBoxIcon.Stop);
        }

        private void FillUserInformation()
        {
            Cursor.Current = Cursors.WaitCursor;
            DataTable dataTable = Utilities.GetAllRegions();
            Cursor.Current = Cursors.Default;
            cboRegions.ValueMember = "RegionID";
            cboRegions.DisplayMember = "RegionName";
            cboRegions.DataSource = dataTable;

            switch (this.user.Level)
            {
                case AccessLevel.ADMINISTRATOR:
                case AccessLevel.NATION:
                    rdoNation.Checked = true;
                    cboRegions.SelectedIndex = 0;
                    break;

                case AccessLevel.REGION:
                    rdoRegion.Checked = true;
                    cboRegions.SelectedValue = user.AccessLevelValue;
                    break;

                case AccessLevel.DISTRICT:
                    rdoDistrict.Checked = true;
                    int districtID = Int32.Parse(user.AccessLevelValue);
                    Cursor.Current = Cursors.WaitCursor;
                    dataTable = Utilities.GetDistrictRegion(districtID);
                    Cursor.Current = Cursors.Default;
                    cboRegions.SelectedValue = dataTable.Rows[0]["RegionID"];
                    cboDistricts.SelectedValue = user.AccessLevelValue;
                    break;

                case AccessLevel.FACILITY:
                    rdoFacility.Checked = true;
                    string facilityCode = user.AccessLevelValue;
                    Cursor.Current = Cursors.WaitCursor;
                    dataTable = Utilities.GetFacilityRegion(facilityCode);
                    cboRegions.SelectedValue = dataTable.Rows[0]["RegionID"];
                    dataTable = Utilities.GetFacilityDistrict(facilityCode);
                    Cursor.Current = Cursors.Default;
                    cboDistricts.SelectedValue = dataTable.Rows[0]["DistrictNo"];
                    cboFacilities.SelectedValue = facilityCode;
                    break;
            }
        }

        private bool ValidSheetName(string sheetName)
        {
            // Example of valid sheet name is Jan 2012
            if (sheetName.Length != 8) return false;
            string month = sheetName.Substring(0, 3);
            string year = sheetName.Substring(4, 4);

            month = month.ToLower();
            switch (month)
            {
                case "jan":
                case "feb":
                case "mar":
                case "apr":
                case "may":
                case "jun":
                case "jul":
                case "aug":
                case "sep":
                case "oct":
                case "nov":
                case "dec":
                    //case "january":
                    //case "february":
                    //case "march":
                    //case "april":
                    //case "june":
                    //case "july":
                    //case "august":
                    //case "september":
                    //case "october":
                    //case "november":
                    //case "december":
                    try
                    {
                        int iYear = Int32.Parse(year);
                        if (iYear > 2000) return true;
                        else return false;
                    }
                    catch
                    {
                        return false;
                    }
                    break;

                default:
                    return false;
                    break;
            }
        }

        private int GetFacilityRow(Excel.Worksheet worksheet)
        {
            for (int i = INITIAL_ROW_INDEX; i <= MAX_ROW_INDEX; i++)
            {
                Excel.Range cells = worksheet.Cells;
                Excel.Range cell = (Excel.Range)cells[i, 1];
                try
                {
                    string text = (string)cell.Value;
                    if (String.IsNullOrEmpty(text)) continue;
                    else
                    {
                        if (text.StartsWith(FACILITY_ROW_START_TEXT)) return i;
                        else continue;
                    }
                }
                catch { }
                finally
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(cell);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(cells);
                    cell = null;
                    cells = null;
                }
            }
            return -1;
        }

        private void ReleaseObject(object obj)
        {
            try
            {
                if (obj == null) return;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show(String.Format("Unable to release the Object {0}", ex.ToString()));
            }
            finally
            {
                GC.Collect();
            }
        }

        private string GetFacilityCode(string facilityName, int districtID)
        {
            // This applies when online
            if (AppConfiguration.AccessMethod == DataAccessMethod.WEB_SERVICES)
            {
                facilityName = facilityName.ToLower();
                foreach (DataRow row in this.facilities.Rows)
                {
                    string name = row["FacName"].ToString();
                    name = name.ToLower();
                    if (name.StartsWith(facilityName))
                    {
                        string code = row["FacCode"].ToString();
                        return code;
                    }
                }
                return null;
            }

            // This applies when offline
            DbCommand command = null;
            switch (AppConfiguration.CacheDBType)
            {
                case CacheDB.MS_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case CacheDB.SQL_SERVER:
                    command = GenericDataAccess.CreateCommand();
                    break;

                default:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;
            }

            command.CommandText = @"SELECT FacCode FROM Facilities WHERE District=@DistrictID 
                AND FacName LIKE @FacName + '%'";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@DistrictID";
            param.DbType = DbType.Int32;
            param.Value = districtID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@FacName";
            param.DbType = DbType.String;
            param.Value = facilityName;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            if (dataTable.Rows.Count == 1)
            {
                DataRow row = dataTable.Rows[0];
                string code = row["FacCode"].ToString();
                return code;
            }
            else return null;
        }
        #endregion

        private void timeElapsedTimer_Tick(object sender, EventArgs e)
        {
            TimeSpan timeSpan = DateTime.Now - startTime;
            lblTimeElapsed.Text = String.Format("Time elapsed: {0:00}:{1:00}:{2:00}",
                timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds);
            timeElapsedTimer.Start();
        }
    }
}
